# Snippets used by Eikos Risk Applications to accelerate VB.Net codebehind file 
# generation for Eikos Online Applications

# Snippets
# --------
# SQL Command and accompanying DataTable
snippet vbsql Datatable 
	Protected dt$1 As New System.Data.DataTable

snippet vbsql SQL Command + Datatable 
	' {{{ dt${1:DataTable}
	Protected dt$1 As New System.Data.DataTable
	Dim sql$1 As String = EOAC.TextFileRead("${2:`substitute(Filename(), "\.[a-zA-Z0-9]*$", "", "")`}_${3:$1}.SQL")

	If not sql$1 = "" Then
		Dim sqlc$1 = New System.Data.SqlClient.SqlCommand
		sqlc$1.CommandText = sql$1
		sqlc$1.Connection = SqlConnection1
		sqlc$1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${4:ParameterName}", System.Data.SqlDbType.${5:Int}, ${6:5}))

		Dim sda$1 As New SqlClient.SqlDataAdapter()
		sda$1.SelectCommand = sqlc$1
		sda$1.SelectCommand.Parameters("@$3").Value = ${7:ParameterValue}
		sda$1.Fill(dt$1)
	Else
		pnlError.Visible = True
		lblError.Text = "There was a problem in setting up database communication on this page ($2)"
	End If
	' }}}

# SQL Command for updates (should rather use a stored procedure)
snippet vbsql SQL Command - Update
	Dim sql${1:SqlCommandName} As String = "" & _
		" UPDATE " & _
		"	${2:TableName}" & _
		" SET " & _
		" WHERE "
	Dim sqlc$1 = New System.Data.SqlClient.SqlCommand
	sqlc$1.CommandText = sql$1
	sqlc$1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${3:FirstParam}", System.Data.SqlDbType.${4:Int}, ${5:4}))
	sqlc$1.Parameters("@$3").Value = ${6:ParameterValue}
	sqlc$1.Connection = SqlConnection1
	sqlc$1.ExecuteNonQuery()

# SQL Command for executing a stored procedure
snippet vbsql SQL Command - Stored Procedure
	Dim sqlc${1:SqlCommandName} = New System.Data.SqlClient.SqlCommand
	sqlc$1.CommandText = "[${2:net_}]"
	sqlc$1.CommandType = System.Data.CommandType.StoredProcedure
	sqlc$1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${3:id}", System.Data.SqlDbType.${4:Int}, ${5:4}))
	sqlc$1.Parameters("@$3").Value = ${6}
	sqlc$1.Connection = SqlConnection1
	sqlc$1.ExecuteNonQuery()

# Snippet for adding an SQL parameter to an SQL Command
snippet vbsql SQL Command Parameter (input)
	sqlc${1:SqlCommand}.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${2:Parameter}", System.Data.SqlDbType.${3:Int}, ${4:4}))

# Snippet for adding an SQL output parameter to an SQL Command
snippet vbsql SQL Command Parameter (output)
	sqlc${1:SqlCommand}.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${2:Parameter}", System.Data.SqlDbType.${3:Int}, ${4:4}, System.Data.ParameterDirection.Output, False, 0, 0, "", DataRowVersion.Default, Nothing))
# General ASP.Net
# Standard Includes, especially required by non-compiled pages (EOA specific)
snippet vbincludes
	Imports Microsoft.VisualBasic
	Imports eikosonline.net
	Imports System.Data

snippet todo
	' TODO: ${1}


# Templates
# Header Page accompanying codebehind
snippet page Header page
	Imports Microsoft.VisualBasic
	Imports eikosonline.net
	Imports System.Data
	
	Public Class ${1:`substitute(substitute(Filename(), "\.[a-zA-Z0-9]*$", "", ""), "header$", "", "")."Header"`}
		Inherits System.Web.UI.Page
		Protected WithEvents SqlConnection1 As New System.Data.SqlClient.SqlConnection
		Protected dt$1 As New System.Data.DataTable
		Protected EOAC As New eikosonline.net.EOACore
	
		Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
			EOAC.DBOpen(SqlConnection1)
			EOAC.CheckAccessError(${7:99999})
	
			' {{{ dt$1
			Dim sql$1 As String = EOAC.TextFileRead("${2:`substitute(Filename(), "\.[a-zA-Z0-9]*$", "", "")`}_$1.SQL")
	
			If not sql$1 = "" Then
				Dim sqlc$1 = New System.Data.SqlClient.SqlCommand
				sqlc$1.CommandText = sql$1
				sqlc$1.Connection = SqlConnection1
				sqlc$1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${3:id}", System.Data.SqlDbType.${4:Int}, ${5:4}))
	
				Dim sda$1 As New SqlClient.SqlDataAdapter()
				sda$1.SelectCommand = sqlc$1
				sda$1.SelectCommand.Parameters("@$3").Value = Session("$3")
				sda$1.Fill(dt$1)
			Else
				pnlError.Visible = True
				lblError.Text = "There was a problem in setting up database communication on this page ($2)"
			End If
			' }}}
			SqlConnection1.Close()
		End Sub
	
		Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender
			If dt$1.Rows.Count > 0 Then
				With dt$1.Rows(0)
					lbl${6:Label1}.Text = .Item("$6")
					lblStatus.Text = .Item("Status")
				End With
			End If
		End Sub
	End Class

# List Page's accompanying codebehind
snippet page List 
	Imports Microsoft.VisualBasic
	Imports eikosonline.net
	Imports System.Data
	
	Public Class ${1:`substitute(substitute(Filename(), "\\.[a-zA-Z0-9]*$", "", ""), "list$", "List", "")`}
	Inherits System.Web.UI.Page
	Protected WithEvents SqlConnection1 As New System.Data.SqlClient.SqlConnection
	'Protected WithEvents pnlError As System.Web.UI.WebControls.Panel
	'Protected WithEvents pnlAll As System.Web.UI.WebControls.Panel
	'Protected WithEvents pnlListForm As System.Web.UI.WebControls.Panel
	'Protected WithEvents lblError As System.Web.UI.WebControls.Label
	'Protected WithEvents btnList As System.Web.UI.WebControls.Button
	'Protected WithEvents btnAdd As System.Web.UI.WebControls.Button
	'Protected WithEvents txt${2:Filter1} As New System.Web.UI.WebControls.TextBox
	'Protected WithEvents DG$1 As System.Web.UI.WebControls.DataGrid
	Protected dt$1 As New System.Data.DataTable
	Protected EOAC As New eikosonline.net.EOACore
	
	Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		EOAC.CheckAccessError(${3:99999})
		btnAdd.Visible = EOAC.CheckAccess(${4:99999})
		
		EOAC.DBOpen(SqlConnection1)
		' {{{ dt$1
		Dim sql$1 As String = EOAC.TextFileRead("$1_${5:EntityTypes}.SQL")
		
		If not sql$1 = "" Then
			Dim sqlc$1 = New System.Data.SqlClient.SqlCommand
			sqlc$1.CommandText = sql$1
			sqlc$1.Connection = SqlConnection1
			' Declaration of the variable(s) to be used for SEARCHING
			sqlc$1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${6:$2}", System.Data.SqlDbType.${7:VarChar}, ${8:30}))
			Dim sda$1 As New SqlClient.SqlDataAdapter()
			sda$1.SelectCommand = sqlc$1
			sda$1.SelectCommand.Parameters("@$6").Value = txt$2.Text
			sda$1.Fill(dt$1)
		Else
			pnlError.Visible = True
			lblError.Text = "There was a problem in setting up database communication on this page ($1_$5)"
		End If
		' }}}
		SqlConnection1.Close
	End Sub
	
	Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
		If EOAC.CheckAccess($3) Then
			Response.Redirect("${9:`substitute($1, "list$", "Add", "")`}.aspx")
		End If
	End Sub
	
	Private Sub DG$1_ItemCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DG$1.ItemCommand
		If e.CommandName = "Select" Then
			Session("${10:$5_id}") = DG$1.DataKeys(e.Item.ItemIndex)
			Response.Redirect("${11:`substitute($1, "list$", "Frames", "")`}.html")
		End If
	End Sub
	
	Private Sub DG$1_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DG$1.SortCommand
		EOAC.DGSortHandling(ViewState, e)
	End Sub
	
	Private Sub DG$1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DG$1.PageIndexChanged
		EOAC.DGPageHandling(source, e)
	End Sub
	
	Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender
		EOAC.DGBind(ViewState, DG$1, dt$1)
	End Sub
	
	End Class

# Add Page's accompanying codebehind
snippet page Add 
	Imports Microsoft.VisualBasic
	Imports eikosonline.net
	Imports System.Data
	
	Public Class ${1:`substitute(substitute(Filename(), "\\.[a-zA-Z0-9]*$", "", ""), "add$", "Add", "")`}
		Inherits System.Web.UI.Page
		Protected WithEvents SqlConnection1 As New System.Data.SqlClient.SqlConnection
		'Protected WithEvents pnlError As System.Web.UI.WebControls.Panel
		'Protected WithEvents pnlAll As System.Web.UI.WebControls.Panel
		'Protected WithEvents lblError As System.Web.UI.WebControls.Label
		'Protected WithEvents btnAdd As System.Web.UI.WebControls.Button
		'Protected WithEvents pnlAdd As System.Web.UI.WebControls.Panel
		Protected EOAC As New eikosonline.net.EOACore
		Protected dt${2:DropDownListEntityName} As New System.Data.DataTable
		
		'Protected WithEvents ddl$2 As System.Web.UI.WebControls.DropDownList
		'Protected WithEvents txt${3:TextBoxName}> As System.Web.UI.WebControls.TextBox
		
		Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
			EOAC.DBOpen(SqlConnection1)
			
			EOAC.CheckAccessError(${4:99999})
			pnlAdd.Visible = EOAC.CheckAccess(${5:99999}) ' Insert facility
			
			EOAC.DBOpen(SqlConnection1)
			If Not IsPostBack Then
				' {{{ dt$2
				Dim sql$2 As String = EOAC.TextFileRead("$1_$2s.SQL")
				
				If not sql$2 = "" Then
					Dim sqlc$2 = New System.Data.SqlClient.SqlCommand
					sqlc$2.CommandText = sql$2
					sqlc$2.Connection = SqlConnection1
					
					Dim sda$2 As New SqlClient.SqlDataAdapter()
					sda$2.SelectCommand = sqlc$2
					sda$2.Fill(dt$2)
				Else
					pnlError.Visible = True
					lblError.Text = "There was a problem in setting up database communication on this page ($1_$2)"
				End If
				' }}}
				ddl$2.DataBind()
			End If
			SqlConnection1.Close
		End Sub
		
		Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
			Page.Validate()
			If Page.IsValid AND EOAC.CheckAccess($5) Then
				EOAC.DBOpen(SqlConnection1)
				Dim sqlc = New System.Data.SqlClient.SqlCommand
				sqlc.CommandText = "[${6:net_Add}]"
				sqlc.CommandType = System.Data.CommandType.StoredProcedure
				sqlc.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${7:$2_id}", System.Data.SqlDbType.${8:Int}, ${9:4}))
				sqlc.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${10:$3}", System.Data.SqlDbType.${11:VarChar}, ${12:100}))
				sqlc.Parameters.Add(New System.Data.SqlClient.SqlParameter("@${13:EntityName}_id", System.Data.SqlDbType.${14:Int}, ${15:4}, System.Data.ParameterDirection.Output, False, 0, 0, "", DataRowVersion.Default, Nothing))
				sqlc.Parameters("@$7").Value = txt$3.Text
				sqlc.Parameters("@$10").Value = ddl$2.SelectedValue
				sqlc.Connection = SqlConnection1
				sqlc.ExecuteNonQuery()
				SqlConnection1.Close
				Session("${16:$12}_id") = sqlc.Parameters("@$13_id").Value
				Response.Redirect("${17:`substitute($1, "add$", "List", "")`}.aspx")
			End If
		End Sub
	End Class

# Event Handlers
snippet vbevent Page_Load
	Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		${1:}
	End Sub

snippet vbevent Page_Prerender
	Private Sub Page_Prerender(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Prerender
		${1:}
	End Sub

snippet vbevent Control_Prerender
	Private Sub ${1:}_Prerender(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ${1:}.Prerender
		${2:}
	End Sub

snippet vbevent Button_Click
	Private Sub ${1:btnButton}_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ${1}.Click
		${2:}
	End Sub

snippet vbevent DropDownList_ValueChanged
	Private Sub ${1:ddlList}_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ${1}.SelectedIndexChanged
		${2:}
	End Sub

# Datagrid Event Handlers
snippet vbdg ItemCommand
	Private Sub DG${1:DataGrid}_ItemCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DG$1.ItemCommand
		If e.CommandName = "Select" Then
			Session("${id}") = DG$1.DataKeys(e.Item.ItemIndex)
			Response.Redirect("${2:`substitute(substitute(Filename(), "\\.[a-zA-Z0-9]*$", "", ""), "\(add\|list\|header\)$", "Frames", "")`}.html")
		End If
	End Sub

snippet vbdg PageIndexChanged
	Private Sub DG${1:DataGrid}_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DG$1.PageIndexChanged
		EOAC.DGPageHandling(source, e)
	End Sub

snippet vbdg SortCommand
	Private Sub DG${1:DataGrid}_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles DG$1.SortCommand
		EOAC.DGSortHandling(ViewState, e)
	End Sub

snippet vbdg PreRender
	Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender
		EOAC.DGBind(ViewState, DG${1:DataGrid}, dt$1)
	End Sub

